package com.gy.boot.rryExcel;

import cn.hutool.core.map.MapUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.sax.handler.RowHandler;
import com.gy.boot.common.util.BlankUtil;
import com.gy.boot.common.util.CommonUtil;
import org.junit.Test;
import java.util.Map;

/** 2023-08-21 王梦蝶 导入客户, 员工签约备案 */
public class ImportContractTmpl {

    /** 导入客户合同
     # 导入客户合同-表头列:
     # 客户名称, 合同编号, 合同类型, 生效时间, 失效时间
     # 注意: 1. 导入历史合同, 不创建合同账户

执行方法:
1. 导出客户数据字典合同类型:
SELECT typename AS service_type_name, typecode AS service_type FROM t_s_type WHERE typegroupid = '297e64045cbe11ad015cbe4aa30f003f';
制作Map {service_type_name, service_type}

2. 导出全部客户名称
SELECT name AS customer_name, customer_id, signing_subject AS signing_subject_id FROM c_customer;
制作Map {customer_name, {customer_id, signing_subject_id}}

     * */
    @Test
    public void testImportCustomerContract() {
        Map<String, String> serviceTypeMap = getServiceTypeMap(); // 数据字典-客户合同类型
        if (serviceTypeMap.isEmpty()) {
            System.out.println("客户合同类型-数据字典-不存在");
            return;
        }
        Map<String, Map<String, String>> customerMap = getCustomerMap(); // 全部客户名称
        if (customerMap.isEmpty()) {
            System.out.println("客户档案-不存在");
            return;
        }
        StringBuilder errMsg = new StringBuilder();
        StringBuilder contractSql = new StringBuilder();
        Map<String, Integer> keyIndex = CommonUtil.getKeyIndex(titleCodeC);
        String path = "C:\\Users\\asus\\Documents\\WeChat Files\\wxid_19q8x5qctowv22\\FileStorage\\File\\2023-03\\全国社保政策录入模板.xlsx";
        ExcelUtil.readBySax(path, 0, readRowCustomerContract(contractSql, keyIndex, serviceTypeMap, customerMap, errMsg));
        System.out.println("INSERT INTO c_contract (customer_id, signing_subject_id, service_type, start_date, end_date, contract_number) VALUES ");
        System.out.println(contractSql);
        System.out.println(errMsg);
    }
    private static final String[] titleCodeC = {"customer_name", "contract_number", "service_type_name", "start_date", "end_date"};
    private RowHandler readRowCustomerContract(StringBuilder contractSql, Map<String, Integer> keyIndex
            , Map<String, String> serviceTypeMap, Map<String, Map<String, String>> customerMap, StringBuilder errMsg) {
        final int[] readRowIndex = {0};
        System.out.println("readRowIndex[0]: " + readRowIndex[0]);
        return (sheetIndex, rowIndex, rowList) -> {
            if (rowIndex < 1 && readRowIndex[0] < 1) {
                readRowIndex[0] += 1;  // 防止二次读取表头数据
                return;
            }
            String customer_name = CommonUtil.getValue(rowList, keyIndex.get("customer_name"));
            String contract_number = CommonUtil.getValue(rowList, keyIndex.get("contract_number"));
            String service_type_name = CommonUtil.getValue(rowList, keyIndex.get("service_type_name"));
            String start_date = CommonUtil.getValue(rowList, keyIndex.get("start_date"));
            String end_date = CommonUtil.getValue(rowList, keyIndex.get("end_date"));
            if (BlankUtil.isBlankNull(customer_name)
                    || BlankUtil.isBlankNull(contract_number)
                    || BlankUtil.isBlankNull(service_type_name)
                    || BlankUtil.isBlankNull(start_date)
                    || BlankUtil.isBlankNull(end_date)) {
                errMsg.append("第").append(rowIndex+1).append("行, 不能导入, 原因: 必填数据不能为空;\n");
                return;
            }
            String service_type = serviceTypeMap.get(service_type_name);
            if (BlankUtil.isBlankNull(service_type)) {
                errMsg.append("第").append(rowIndex+1).append("行, 不能导入, 原因: 客户合同类型不存在;\n");
                return;
            }
            Map<String, String> customer = customerMap.get(customer_name);
            if (customer == null || customer.isEmpty()) {
                errMsg.append("第").append(rowIndex+1).append("行, 不能导入, 原因: 客户档案不存在;\n");
                return;
            }
            String customer_id = customer.get("customer_id");
            if (BlankUtil.isBlankNull(customer_id)) {
                errMsg.append("第").append(rowIndex+1).append("行, 不能导入, 原因: 客户档案不存在;\n");
                return;
            }
            String signing_subject_id = customer.get("signing_subject_id");
            if (BlankUtil.isBlankNull(signing_subject_id)) {
                errMsg.append("第").append(rowIndex+1).append("行, 不能导入, 原因: 客户档案签约主体不存在;\n");
                return;
            }
            // (customer_id, signing_subject_id, service_type, start_date, end_date, contract_number)
            contractSql.append("(").append(customer_id).append(",").append(signing_subject_id)
                    .append(",").append(service_type).append(",").append(start_date)
                    .append(",").append(end_date).append(",").append(contract_number)
                    .append("),\n");
        };
    }

    /**
1. 导出数据字典-客户合同类型:
SELECT typename AS service_type_name, typecode AS service_type FROM t_s_type WHERE typegroupid = '297e64045cbe11ad015cbe4aa30f003f';
制作Map {service_type_name, service_type}
     * */
    private Map<String, String> getServiceTypeMap() {
        Map<String, String> serviceTypeMap = MapUtil.newHashMap(100);
        String path = "C:/Users/asus/Downloads/20230821test_客户合同类型数据字典.xlsx";
        ExcelUtil.readBySax(path, 0, readRowTypeData(serviceTypeMap)); // 客户合同类型数据字典
        return serviceTypeMap;
    }
    private RowHandler readRowTypeData(Map<String, String> serviceTypeMap) {
        return (sheetIndex, rowIndex, rowList) -> {
            String typename = CommonUtil.getValue(rowList, 0);
            String typecode = CommonUtil.getValue(rowList, 1);
            if (BlankUtil.isBlankNull(typename) || BlankUtil.isBlankNull(typecode)) {
                return;
            }
            serviceTypeMap.put(typename, typecode);
        };
    }

    /**
 2. 导出全部客户名称
 SELECT name AS customer_name, customer_id, signing_subject AS signing_subject_id FROM c_customer;
 制作Map {customer_name, {customer_id, signing_subject_id}}
 * */
    private Map<String, Map<String, String>> getCustomerMap() {
        Map<String, Map<String, String>> customerMap = MapUtil.newHashMap(1000);
        String path = "C:/Users/asus/Downloads/20230821test_客户档案.xlsx";
        ExcelUtil.readBySax(path, 0, readRowCustomerMap(customerMap));
        return customerMap;
    }
    private RowHandler readRowCustomerMap(Map<String, Map<String, String>> customerMap) {
        return (sheetIndex, rowIndex, rowList) -> {
            String customer_name = CommonUtil.getValue(rowList, 0);
            String customer_id = CommonUtil.getValue(rowList, 1);
            String signing_subject_id = CommonUtil.getValue(rowList, 2);
            if (BlankUtil.isBlankNull(customer_name) || BlankUtil.isBlankNull(customer_id) || BlankUtil.isBlankNull(signing_subject_id)) {
                return;
            }
            Map<String, String> params = MapUtil.newHashMap(4);
            params.put("customer_name", customer_name);
            params.put("customer_id", customer_id);
            params.put("signing_subject_id", signing_subject_id);
            customerMap.put(customer_name, params);
        };
    }







   /** 导入员工签约备案

    导入员工合同-表头列:
    员工姓名, 员工证件号, 合同编号, 签约主体, 合同类型, 生效时间, 失效时间, 工资基数, 试用基数, 用工单位
    注意: 1. "试用基数" 和 "用工单位" 可以不填;

    执行方法:
    1. 导出数据字典员工合同类型:
    SELECT typename AS contract_type, typecode AS service_type FROM t_s_type WHERE typegroupid = 'ff80808152a0c6ad015307b811dsda';
    制作Map {service_type_name, service_type}

    2. 导出全部客户名称-用于用工单位
    SELECT name AS customer_name, customer_id, signing_subject AS signing_subject_id FROM c_customer;
    制作Map {customer_name, {customer_id, signing_subject_id}}

    3. 导出全部员工数据-用户获取员工ID
    SELECT staff_id, ID_number FROM s_staff;

    4. 导出全部签约主体-获取签约主体ID
    SELECT signing_subject_id, signing_subject_name FROM t_signing_subject;
    * */
   @Test
   public void testImportStaffContract() {
       Map<String, String> contractTypeMap = getContractTypeMap(); // 数据字典-客户合同类型
       if (contractTypeMap.isEmpty()) {
           System.out.println("员工合同类型-数据字典-不存在");
           return;
       }
       Map<String, String> staffMap = getStaffMap();
       if (staffMap.isEmpty()) {
           System.out.println("员工档案-不存在");
           return;
       }
       Map<String, String> subjectMap = getSubjectMap();
       if (subjectMap.isEmpty()) {
           System.out.println("签约主体-不存在");
           return;
       }
       Map<String, Map<String, String>> customerMap = getCustomerMap(); // 用工单位-获取全部客户名称
       if (customerMap.isEmpty()) {
           System.out.println("客户档案-不存在");
           return;
       }
       StringBuilder errMsg = new StringBuilder();
       StringBuilder contractSql = new StringBuilder();
       Map<String, Integer> keyIndex = CommonUtil.getKeyIndex(titleCodeS);
       String path = "C:\\Users\\asus\\Documents\\WeChat Files\\wxid_19q8x5qctowv22\\FileStorage\\File\\2023-03\\全国社保政策录入模板.xlsx";
       ExcelUtil.readBySax(path, 0, readRowStaffContract(contractSql, keyIndex, contractTypeMap, customerMap, staffMap, subjectMap, errMsg));
       System.out.println("INSERT INTO s_contract (staff_id, contract_name, contract_num, contract_type, start_date, end_date, actual_end_date, wage_base, probation_base, signing_subject_id, customer_id) VALUES ");
       System.out.println(contractSql);
       System.out.println(errMsg);
   }
    private static final String[] titleCodeS = {"员工姓名", "员工证件号", "合同编号", "签约主体", "合同类型", "生效时间", "失效时间", "工资基数", "试用基数", "用工单位"};
    private RowHandler readRowStaffContract(StringBuilder contractSql, Map<String, Integer> keyIndex
            , Map<String, String> contractTypeMap, Map<String, Map<String, String>> customerMap
            , Map<String, String> staffMap, Map<String, String> subjectMap, StringBuilder errMsg) {
        final int[] readRowIndex = {0};
        System.out.println("readRowIndex[0]: " + readRowIndex[0]);
        return (sheetIndex, rowIndex, rowList) -> {
            if (rowIndex < 1 && readRowIndex[0] < 1) {
                readRowIndex[0] += 1;  // 防止二次读取表头数据
                return;
            }
            String staff_name = CommonUtil.getValue(rowList, keyIndex.get("员工姓名"));
            String ID_number = CommonUtil.getValue(rowList, keyIndex.get("员工证件号"));
            String contract_num = CommonUtil.getValue(rowList, keyIndex.get("合同编号"));
            String contract_type_name = CommonUtil.getValue(rowList, keyIndex.get("合同类型"));
            String signing_subject_name = CommonUtil.getValue(rowList, keyIndex.get("签约主体"));
            String start_date = CommonUtil.getValue(rowList, keyIndex.get("生效时间"));
            String end_date = CommonUtil.getValue(rowList, keyIndex.get("失效时间"));
            String wage_base = CommonUtil.getValue(rowList, keyIndex.get("工资基数"));
            String probation_base = CommonUtil.getValue(rowList, keyIndex.get("试用基数"));
            String customer_name = CommonUtil.getValue(rowList, keyIndex.get("用工单位"));
            if (BlankUtil.isBlankNull(staff_name)
                    || BlankUtil.isBlankNull(ID_number)
                    || BlankUtil.isBlankNull(contract_num)
                    || BlankUtil.isBlankNull(contract_type_name)
                    || BlankUtil.isBlankNull(signing_subject_name)
                    || BlankUtil.isBlankNull(wage_base)
                    || BlankUtil.isBlankNull(start_date)
                    || BlankUtil.isBlankNull(end_date)) {
                errMsg.append("第").append(rowIndex+1).append("行, 不能导入, 原因: 必填数据为空;\n");
                return;
            }
            String contract_type = contractTypeMap.get(contract_type_name);
            if (BlankUtil.isBlankNull(contract_type)) {
                errMsg.append("第").append(rowIndex+1).append("行, 不能导入, 原因: 员工合同类型数据字典不存在;\n");
                return;
            }
            String staff_id = staffMap.get(ID_number);
            if (BlankUtil.isBlankNull(staff_id)) {
                errMsg.append("第").append(rowIndex+1).append("行, 不能导入, 原因: 员工档位不存在;\n");
                return;
            }
            String signing_subject_id = subjectMap.get(signing_subject_name);
            if (BlankUtil.isBlankNull(staff_id)) {
                errMsg.append("第").append(rowIndex+1).append("行, 不能导入, 原因: 签约主体不存在;\n");
                return;
            }
            String customer_id = null;
            if (!BlankUtil.isBlankNull(customer_name)) {
                Map<String, String> customer = customerMap.get(customer_name);
                customer_id = customer == null ? null : customer.get("customer_id");
                if (BlankUtil.isBlankNull(customer_id)) {
                    errMsg.append("第").append(rowIndex+1).append("行, 不能导入, 原因: 用工单位不存在;\n");
                    return;
                }
            }
//          (staff_id, contract_name, contract_num, contract_type, start_date, end_date, actual_end_date, wage_base, probation_base, signing_subject_id, customer_id)
            customer_id = BlankUtil.isBlank(customer_id) ? "null" : customer_id;
            probation_base = BlankUtil.isBlank(probation_base) ? "null" : probation_base;
            contractSql.append("(").append(staff_id).append(",").append(contract_type_name).append(",").append(contract_num)
                    .append(",").append(contract_type).append(",").append(start_date).append(",").append(end_date).append(",").append(end_date)
                    .append(",").append(wage_base).append(",").append(probation_base).append(",").append(signing_subject_id)
                    .append(",").append(customer_id).append("),\n");
        };
    }
   /**
    1. 导出数据字典员工合同类型:
    SELECT typename AS contract_type, typecode AS service_type FROM t_s_type WHERE typegroupid = 'ff80808152a0c6ad015307b811dsda';
    制作Map {service_type_name, service_type}
    * */
   private Map<String, String> getContractTypeMap() {
       Map<String, String> contractTypeMap = MapUtil.newHashMap(100);
       String path = "C:/Users/asus/Downloads/20230821test_员工合同类型数据字典.xlsx";
       ExcelUtil.readBySax(path, 0, readRowTypeData(contractTypeMap)); // 员工合同类型数据字典
       return contractTypeMap;
   }

    /**
     3. 导出全部员工数据-用户获取员工ID
     SELECT staff_id, ID_number FROM s_staff;
     * */
    private Map<String, String> getStaffMap() {
        Map<String, String> staffMap = MapUtil.newHashMap(100);
        String path = "C:/Users/asus/Downloads/20230821test_员工数据.xlsx";
        ExcelUtil.readBySax(path, 0, readRowStaffMap(staffMap)); // 员工合同类型数据字典
        return staffMap;
    }
    private RowHandler readRowStaffMap(Map<String, String> staffMap) {
        return (sheetIndex, rowIndex, rowList) -> {
            String staff_id = CommonUtil.getValue(rowList, 0);
            String ID_number = CommonUtil.getValue(rowList, 1);
            if (BlankUtil.isBlankNull(staff_id) || BlankUtil.isBlankNull(ID_number)) {
                return;
            }
            staffMap.put(ID_number, staff_id);
        };
    }

    /**
     4. 导出全部签约主体-获取签约主体ID
     SELECT signing_subject_id, signing_subject_name FROM t_signing_subject;
     * */
    private Map<String, String> getSubjectMap() {
        Map<String, String> staffMap = MapUtil.newHashMap(100);
        String path = "C:/Users/asus/Downloads/20230821test_签约主体.xlsx";
        ExcelUtil.readBySax(path, 0, readRowSubjectMap(staffMap)); // 员工合同类型数据字典
        return staffMap;
    }
    private RowHandler readRowSubjectMap(Map<String, String> staffMap) {
        return (sheetIndex, rowIndex, rowList) -> {
            String signing_subject_id = CommonUtil.getValue(rowList, 0);
            String signing_subject_name = CommonUtil.getValue(rowList, 1);
            if (BlankUtil.isBlankNull(signing_subject_id) || BlankUtil.isBlankNull(signing_subject_name)) {
                return;
            }
            staffMap.put(signing_subject_name, signing_subject_id);
        };
    }


}
